Submit the following two files:
Formatting Python Code When programming in Python, refer to Kenneth Reitz’ PEP 8: The Style Guide for Python Code: http://pep8.org/ (Links to an external site.)Links to an external site. There is the Google style guide for Python at https://google.github.io/styleguide/pyguide.html (Links to an external site.)Links to an external site. Comment often and in detail.
In his first state of the uniion address , president Trump mentioned Chicago violance 10 times Trump's State of the Union Address
Columnist Clarence Page wrote an article , published by the Chicago Tribune stated that the city of Chicago had more homicides in the past two years than New York and Los Angeles combined
The CSV file for crimes dataset for the city of Chicago is obtained from the data portal for the city of Chicago. Here is the link for the city of Chicago data portal City of Chicago Data Portal
Three datasets are need for this assignment:
Lets load the CSV file into a DataFrame object and see the nature of the data that we have.
Complete description of the dataset can be found on Chicago city data portal.
Based on Trumps State of the Union Address and the article written by columnist Clarence Page and published by the Chicago Tribune, we are interested to retrieve the data for the past two years and perform different types of spatial queries.
There are few of these queries that we are interested in to help CPD and city of Chicago to plot on a Choropleteh map those districts that have highest gun crimes.
Here are examples of those types of queries:
Packages you need to Connect PostgreSQL server to load and retrieve Crhicago Crime dataset from the database:
Since we are using PostGIS in our work, please read and bookmark Chapter 4. Using PostGIS: Data Management and Queries
!pip install psycopg2
Collecting psycopg2 Downloading https://files.pythonhosted.org/packages/5c/1c/6997288da181277a0c29bc39a5f9143ff20b8c99f2a7d059cfb55163e165/psycopg2-2.8.3.tar.gz (377kB) 100% |████████████████████████████████| 378kB 6.6MB/s eta 0:00:01 Building wheels for collected packages: psycopg2 Building wheel for psycopg2 (setup.py) ... done Stored in directory: /Users/EdwardArroyo/Library/Caches/pip/wheels/48/06/67/475967017d99b988421b87bf7ee5fad0dad789dc349561786b Successfully built psycopg2 Installing collected packages: psycopg2 Successfully installed psycopg2-2.8.3
!pip install area
!pip install folium
import folium
from folium import plugins
from folium.plugins import MarkerCluster
import psycopg2
import csv
import pandas as pd
import json
from area import area
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import getpass
pw = getpass.getpass("Password: ")
db_connection = psycopg2.connect(host='129.105.208.229',dbname="chicago_crimes", user="agc4488" , password=pw)
cursor = db_connection.cursor()
db_connection.commit()
The Crimes_2001_to_present.csv is downloaded from Chicago data portal and it has roughly 6.5 million records.
While working on this dataset, It is prudent to make a note of the following:
Sort algorithms used by the database engines vary in performance between O($N log N$) and O($ N^{2} $) where $N$ is the size of the number
Search algorithms used by the database engines vary in performance between O($log N$) and O($ N $) where $N$ is the size of the number
cursor.execute("SELECT district, count(district) from crimes GROUP BY district")
rows=cursor.fetchall()
crimes_per_district = pd.DataFrame(rows, columns=['dist_num','number_of_crimes'])
crimes_per_district['dist_num'] = crimes_per_district['dist_num'].astype(str)
crimes_per_district.head()
crimes_per_district_map = folium.Map(location =(41.8781, -87.6298),zoom_start=11)
## This will yield a warning about choropleth being deprecated in future version...
# crimes_per_district_map.choropleth(geo_data="Boundaries.geojson",
# fill_color='OrRd',
# fill_opacity=0.5,
# line_opacity=1,
# data = crimes_per_district,
# key_on='feature.properties.dist_num',
# columns = ['dist_num', 'number_of_crimes'],
# legend_name="CRIME MAP"
# )
# I pasted a screenshot of this warning in the next (markdown cell)
## Use this this instead of the code in last cell. Note "choropleth" is now "Choropletth".
folium.Choropleth(geo_data="Boundaries.geojson",
fill_color='OrRd',
fill_opacity=0.5,
line_opacity=1,
data = crimes_per_district,
key_on='feature.properties.dist_num',
columns = ['dist_num', 'number_of_crimes'],
legend_name="CRIMES PER DISTRICT"
).add_to(crimes_per_district_map)
crimes_per_district_map
cursor.execute("""SELECT ST_X(ST_AsText(Where_IS)), ST_Y(ST_AsText(Where_IS)), district from police_stations where district!='Headquarters'""")
police_stations = cursor.fetchall()
for police_station in police_stations:
police_station_location = (police_station[0],police_station[1])
cursor.execute('''
SELECT district, count(district)
FROM crimes
WHERE district= %s
GROUP BY district
''',[police_station[2]])
districts_crime_numbers = cursor.fetchall()
#print(districts_crime_numbers)
for district in districts_crime_numbers:
folium.Marker(location =
police_station_location,
popup=folium.Popup(
html="District No : %s has Total Number of Crimes:%s" %district ,max_width=450)).add_to(crimes_per_district_map)
# db_connection.commit()
crimes_per_district_map
N.B: A crime is considered a violent crime if the PRIMARY_TYPE of the crimes is THEFT ,ASSAULT,ROBBER,KIDNAPPING,CRIM SEXUAL ASSAULT,BATTERY, or MURDER.
violent_crime_categories='THEFT','ASSAULT','ROBBERY','KIDNAPPING','CRIM SEXUAL ASSAULT','BATTERY','MURDER'
cursor.execute('''
SELECT district, count(district)
FROM crimes
WHERE PRIMARY_TYPE in %s
GROUP BY district
''',[violent_crime_categories])
rows=cursor.fetchall()
violent_crimes_per_district=pd.DataFrame(rows, columns=['dist_num','number_of_violent_crimes'])
violent_crimes_per_district['dist_num'] = violent_crimes_per_district['dist_num'].astype(str)
violent_crimes_per_district
# violent_crimes_per_district_map= folium.Map(location =(41.8781, -87.6298),zoom_start=11)
# violent_crimes_per_district_map.choropleth(geo_data="Boundaries.geojson",
# fill_color='YlOrRd',
# fill_opacity=0.5,
# line_opacity=1,
# data = violent_crime_per_district,
# key_on='feature.properties.dist_num',
# columns = ['district_num', 'number_of_violent_crimes'],
# legend_name="VOILENT CRIME MAP"
# )
violent_crimes_per_district_map= folium.Map(location =(41.8781, -87.6298),zoom_start=11)
folium.Choropleth(geo_data="Boundaries.geojson",
fill_color='OrRd',
fill_opacity=0.5,
line_opacity=1,
data = violent_crimes_per_district,
key_on='feature.properties.dist_num',
columns = ['dist_num', 'number_of_violent_crimes'],
legend_name="VIOLENT CRIMES PER DISTRICT"
).add_to(violent_crimes_per_district_map)
violent_crimes_per_district_map
In addition, for each district find the block(s) that has the highest number of gun crimes in that district. Note that there might be a tie for the highest number of gun crimes. You need to find all such blocks. Add a popup marker (located at that district's police headquarter) that displays a DataFrame containing all such block along with the number of gun crimes for that block (i.e. the highest number of crimes for a district).
cursor.execute("""SELECT ST_X(ST_AsText(Where_IS)), ST_Y(ST_AsText(Where_IS)), district from police_stations where district!='Headquarters'""")
police_stations = cursor.fetchall()
for police_station in police_stations:
police_station_location =(police_station[0],police_station[1])
cursor.execute('''
SELECT PRIMARY_TYPE, count(PRIMARY_TYPE)
FROM crimes
WHERE district =%s AND PRIMARY_TYPE in %s
GROUP BY PRIMARY_TYPE
''',[police_station[2],violent_crime_categories])
data = cursor.fetchall()
# print(data)
violent_crimes_per_district = pd.DataFrame(data, columns=['Description', 'Number of Violent Crimes'])
header = violent_crimes_per_district.to_html(classes='table table-striped table-hover table-condensed table-responsive')
folium.Marker(location=police_station_location, popup=folium.Popup(html="District Number %s - Violent Crimes %s" %(police_station[2],header))).add_to(violent_crimes_per_district_map)
# db_connection.commit()
violent_crimes_per_district_map
N.B: A crime is considered a gun related violent crime if the word "gun" is contained in the DESCRIPTION and the PRIMARY_TYPE of the crimes is THEFT ,ASSAULT,ROBBER,KIDNAPPING,CRIM SEXUAL ASSAULT,BATTERY, or MURDER.
Lets first create a dataframe of violent gun crimes per district.
gun='%GUN%'
cursor.execute('''
SELECT district, count(district)
FROM crimes
WHERE DESCRIPTION::text LIKE %s and PRIMARY_TYPE in %s
GROUP BY district
''',[gun,violent_crime_categories])
rows = cursor.fetchall()
violent_gun_crimes_per_district = pd.DataFrame(rows, columns=['dist_num','number_of_violent_gun_crimes'])
violent_gun_crimes_per_district['dist_num'] = violent_gun_crimes_per_district['dist_num'].astype(str)
violent_gun_crimes_per_district
# violent_gun_crimes_per_district_map= folium.Map(location =(41.8781, -87.6298),zoom_start=11)
# violent_gun_crimes_per_district_map.choropleth(geo_data="Boundaries.geojson",
# fill_color='YlOrRd',
# fill_opacity=0.5,
# line_opacity=1,
# data = violent_gun_crimes_per_district,
# key_on='feature.properties.dist_num',
# columns = ['dist_num', 'number_of_violent_gun_crimes'],
# legend_name="VIOLENT GUN CRIMES PER DISTRICT"
# )
violent_gun_crimes_per_district_map= folium.Map(location =(41.8781, -87.6298),zoom_start=11)
folium.Choropleth(geo_data="Boundaries.geojson",
fill_color='OrRd',
fill_opacity=0.5,
line_opacity=1,
data = violent_gun_crimes_per_district,
key_on='feature.properties.dist_num',
columns = ['dist_num', 'number_of_violent_gun_crimes'],
legend_name="VIOLENT GUN CRIMES PER DISTRICT"
).add_to(violent_gun_crimes_per_district_map)
violent_gun_crimes_per_district_map
Now, lets create a dataframe of the different types of violent gun crimes for every district and then plot it on Choropleth map
cursor.execute("""SELECT ST_X(ST_AsText(Where_IS)), ST_Y(ST_AsText(Where_IS)), district from police_stations where district!='Headquarters'""")
gun='%GUN%'
police_stations = cursor.fetchall()
for police_station in police_stations:
police_station_location = (police_station[0],police_station[1])
cursor.execute('''
SELECT DESCRIPTION, count(DESCRIPTION)
FROM crimes
WHERE district=%s and DESCRIPTION::text LIKE %s and PRIMARY_TYPE in %s
GROUP BY DESCRIPTION
''',[police_station[2],gun, violent_crime_categories])
data=cursor.fetchall()
df = pd.DataFrame(data, columns=['Description', 'Number of Violent Gun Crimes'])
header = df.to_html(classes='table table-striped table-hover table-condensed table-responsive')
folium.Marker(location=police_station_location,popup=folium.Popup(html="District No: %s GUN_Crime: %s" %(police_station[2],header) )).add_to(violent_gun_crimes_per_district_map)
# db_connection.commit()
violent_gun_crimes_per_district_map
district=[]
tarea=[]
with open('Boundaries.geojson') as f:
data = json.load(f)
a = data['features'] # a is a list of district data (dictionaries)
for i in range(len(a)):# a[i] is the dictionary for ith district in Boundaries.geojson
obj=a[i]['geometry'] # list of coordinates for ith district
n= a[i]['properties'] # district number and district label for ith district
district.append(n['dist_num']) # add district number to the district list
# 1 square meter = 1/10000 hectares. area(obj) is in hectares
tarea.append(area(obj)/10000) # add the the area (in hectares) to area list
af=pd.DataFrame({'dist_num': district,'district_area_in_Hectares':tarea})
af['dist_num'] = af['dist_num'].astype(str)
final_data= pd.merge(af, crimes_per_district, on='dist_num', how='inner')
final_data['crime_density'] = round(final_data['number_of_crimes']/(final_data['district_area_in_Hectares']/100))
final_data
# gun_crime_arrests_map = folium.Map(location =(41.8781, -87.6298),zoom_start=11)
# gun_crime_arrests_map.choropleth(geo_data="Boundaries.geojson",
# fill_color='YlOrRd',
# fill_opacity=0.5,
# line_opacity=1,
# data = violent_gun_crimes_per_district,
# key_on='feature.properties.dist_num',
# columns = ['dist_num', 'number_of_violent_gun_crimes'],
# legend_name="GUN CRIME"
# )
gun_crime_arrests_map = folium.Map(location =(41.8781, -87.6298),zoom_start=11)
folium.Choropleth(geo_data="Boundaries.geojson",
fill_color='OrRd',
fill_opacity=0.5,
line_opacity=1,
data = violent_gun_crimes_per_district,
key_on='feature.properties.dist_num',
columns = ['dist_num', 'number_of_violent_gun_crimes'],
legend_name="VIOLENT GUN CRIMES PER DISTRICT"
).add_to(gun_crime_arrests_map)
gun_crime_arrests_map
cursor.execute("""SELECT ST_X(ST_AsText(Where_IS)), ST_Y(ST_AsText(Where_IS)), district from police_stations where district!='Headquarters'""")
gun='%GUN%'
police_stations = cursor.fetchall()
marker_cluster = MarkerCluster().add_to(gun_crime_arrests_map)
for police_station in police_stations:
police_station_location = (police_station[0],police_station[1])
cursor.execute('''
SELECT DISTINCT ON(caseno)
caseno, block,DESCRIPTION, count(arrest), arrest,latitude, longitude
FROM crimes
WHERE district=%s and DESCRIPTION::text LIKE %s GROUP BY caseno,block,
DESCRIPTION,arrest, latitude, longitude
''',[police_station[2],gun])
crimes_per_district = cursor.fetchall()
for crime in crimes_per_district:
if crime[4]==True:
folium.Marker(location=(crime[5],crime[6]),popup=folium.Popup(html="District No: %s <br> Description: %s <br> Block: %s" %(police_station[2],crime[2],crime[1])),icon=folium.Icon(color='green', icon='ok-sign'),).add_to(marker_cluster)
else:
folium.Marker(location=(crime[5],crime[6]),popup=folium.Popup(html="District No: %s <br> Description: %s<br> Block: %s" %(police_station[2],crime[2],crime[1])),icon=folium.Icon(color='red', icon='remove-sign'),).add_to(marker_cluster)
db_connection.commit()
def embed_map(m):
from IPython.display import IFrame
m.save('index.html')
return IFrame('index.html', width='100%', height='750px')
embed_map(gun_crime_arrests_map)
Locate the farthest gun crime from the police station in every district. Create a Choropleth map where the districts are shaded in proportion to the number of gun crimes in that district. For each district, find the gun crime that was farthest from police station. Add a pop-up on the Choropleth map to display the district number and the Block where the farthest gun crime occurred. Also add circle marker (of radius 5) at the location of the farthest** gun crime.
gun='%GUN%'
cursor.execute('''
SELECT district, count(district)
FROM crimes
WHERE DESCRIPTION::text LIKE %s
GROUP BY district
''',[gun])
rows=cursor.fetchall()
gun_crimes_per_district = pd.DataFrame(rows, columns=['dist_num','number_of_gun_crimes'])
gun_crimes_per_district['dist_num'] = gun_crimes_per_district['dist_num'].astype(str)
gun_crimes_per_district.head()
# farthest_block_gun_crime_map = folium.Map(location =(41.8781, -87.6298),zoom_start=11)
# farthest_block_gun_crime_map.choropleth(geo_data="Boundaries.geojson",
# fill_color='YlOrRd',
# fill_opacity=0.5,
# line_opacity=1,
# data = gun_crimes_per_district,
# key_on='feature.properties.dist_num',
# columns = ['dist_num', 'number_of_gun_crimes'],
# legend_name="GUN CRIMES PER DISTRICT"
# )
# farthest_block_gun_crime_map
farthest_block_gun_crime_map = folium.Map(location =(41.8781, -87.6298),zoom_start=11)
folium.Choropleth(geo_data="Boundaries.geojson",
fill_color='OrRd',
fill_opacity=0.5,
line_opacity=1,
data = gun_crimes_per_district,
key_on='feature.properties.dist_num',
columns = ['dist_num', 'number_of_gun_crimes'],
legend_name="GUN CRIMES PER DISTRICT"
).add_to(farthest_block_gun_crime_map )
farthest_block_gun_crime_map
cursor.execute("""SELECT DISTINCT ST_X(ST_AsText(Where_IS)), ST_Y(ST_AsText(Where_IS)), district from police_stations where district!='Headquarters'""")
police_stations = cursor.fetchall()
gun='%GUN%'
for police_station in police_stations:
cursor.execute("""SELECT DISTINCT on (A.block) A.district, A.block, A.where_is,
ST_Distance(A.where_is,B.where_is)
FROM crimes as A, police_stations as B
WHERE
A.district=%s and DESCRIPTION::text LIKE %s and B.district= %s
and ST_Distance(A.where_is,B.where_is)
IN
( SELECT max(dist)
FROM
(SELECT ST_Distance(A.where_is,B.where_is) as dist
FROM crimes as A, police_stations as B
WHERE A.district=%s and DESCRIPTION::text LIKE %s and B.district= %s ) as f)
""",[police_station[2],gun,police_station[2],police_station[2],gun, police_station[2]])
farthest_block_gun_crime = cursor.fetchall()
print(farthest_block_gun_crime)
print((farthest_block_gun_crime[0][2],farthest_block_gun_crime[0][2]))
if not farthest_block_gun_crime:
continue
cursor.execute('''
SELECT ST_X(ST_AsText(%s)), ST_Y(ST_AsText(%s))
''',(farthest_block_gun_crime[0][2],farthest_block_gun_crime[0][2]))
farthest_block_gun_crime_location = cursor.fetchall()
folium.Marker(location=(police_station[0],police_station[1]),popup=folium.Popup(html="Police Station <br> District No.:%s <br> Farthest Gun_Crime Block:%s"%(farthest_block_gun_crime[0][0],farthest_block_gun_crime[0][1]))).add_to(farthest_block_gun_crime_map)
folium.CircleMarker(farthest_block_gun_crime_location[0],radius=5,color='#ff3187',popup=folium.Popup(html="District No.:%s <br> Block:%s"%(farthest_block_gun_crime[0][0],farthest_block_gun_crime[0][1]))).add_to(farthest_block_gun_crime_map)
print(farthest_block_gun_crime)
print((farthest_block_gun_crime[0][2],farthest_block_gun_crime[0][2]))
db_connection.commit()
farthest_block_gun_crime_map
The HTML document your are submitting along with this notebook file must have the source code and the output for the following requirements
blocks_gun_crime_map2 = folium.Map(location =(41.8781, -87.6298),zoom_start=11)
blocks_gun_crime_map2.choropleth(geo_data="Boundaries.geojson",
fill_color='YlOrRd',
fill_opacity=0.5,
line_opacity=1,
data = gun_crimes_per_district,
key_on='feature.properties.dist_num',
columns = ['dist_num', 'number_of_gun_crimes'],
legend_name="GUN CRIME"
)
#cursor.execute("""SELECT ST_X(ST_AsText(Where_IS)), ST_Y(ST_AsText(Where_IS)), district from police_stations where district!='Headquarters'""")
# find highest gun crime block in each district
gun='%GUN%'
blocks_gun_violent_crimes2 = cursor.fetchall()
blocks_gun_violent_crimes_df2 = pd.DataFrame(blocks_gun_violent_crimes2, columns=['block','district','gun_crimes'])
df3 = pd.DataFrame(blocks_gun_violent_crimes2, columns=['block','district','gun_crimes'])
df4 = pd.DataFrame(blocks_gun_violent_crimes2, columns=['block','district','gun_crimes'])
for district in [x for x in range(1,26) if x not in [13,21,23]]:
cursor.execute("SELECT block, district, count(block) from crimes where DESCRIPTION::text LIKE %s GROUP BY block, district"
,[gun])
df2 = blocks_gun_violent_crimes_df2.query(f'district=={district}')
block_gun_violent_crimes_df3 = df2[df2.gun_crimes == df2.gun_crimes.max()]
header = block_gun_violent_crimes_df3.to_html(classes='table table-striped table-hover table-condensed table-responsive')
folium.Marker(location=police_station_location,popup=folium.Popup(html="District No: %s Block with Highest Gun Crimes: %s" %(police_station[2],header))).add_to(blocks_gun_crime_map2)
print(block_gun_violent_crimes_df3)
df4 = df3.append(block_gun_violent_crimes_df3)
df3.head()
df4 = pd.DataFrame(blocks_gun_violent_crimes2, columns=['block','district','gun_crimes'])
df5 = df3[(df3['district'] == 25) & (df3['gun_crimes'] == 26)]
df5
header = df5.to_html(classes='table table-striped table-hover table-condensed table-responsive')
folium.Marker(location=police_station_location,popup=folium.Popup(html="District No: %s Block with Highest Gun Crimes: %s" %(police_station[2],header))).add_to(blocks_gun_crime_map2)
blocks_gun_crime_map2
gun='%GUN%'
cursor.execute('''
SELECT district, count(district)
FROM crimes
WHERE DESCRIPTION::text LIKE %s
GROUP BY district
''',[gun])
rows = cursor.fetchall()
gun_crimes_per_district = pd.DataFrame(rows, columns=['dist_num','number_of_gun_crimes'])
gun_crimes_per_district['dist_num'] = gun_crimes_per_district['dist_num'].astype(str)
district=[]
tarea=[]
with open('Boundaries.geojson') as f:
data = json.load(f)
a = data['features'] # a is a list of district data (dictionaries)
for i in range(len(a)):# a[i] is the dictionary for ith district in Boundaries.geojson
obj=a[i]['geometry'] # list of coordinates for ith district
n= a[i]['properties'] # district number and district label for ith district
district.append(n['dist_num']) # add district number to the district list
# 1 square meter = 1/10000 hectares. area(obj) is in hectares
tarea.append(area(obj)/10000) # add the the area (in hectares) to area list
af=pd.DataFrame({'dist_num': district,'district_area_in_Hectares':tarea})
af['dist_num'] = af['dist_num'].astype(str)
final_data2= pd.merge(af, gun_crimes_per_district, on='dist_num', how='inner')
final_data2['crime_density'] = round(final_data2['number_of_gun_crimes']/(final_data2['district_area_in_Hectares']/100))
final_data2
handguns='UNLAWFUL POSS OF HANDGUN'
cursor.execute("SELECT district, count(district) from crimes where DESCRIPTION::text LIKE %s GROUP BY district",[handguns])
unlawful_poss_handguns = cursor.fetchall()
unlawful_poss_handguns_df = pd.DataFrame(unlawful_poss_handguns, columns=['dist_num','unlawful_poss_handguns'])
unlawful_poss_handguns_df['dist_num'] = unlawful_poss_handguns_df['dist_num'].astype(str)
unlawful_poss_handguns_df
farthest_block_unlawful_poss_handguns_map = folium.Map(location =(41.8781, -87.6298),zoom_start=11)
folium.Choropleth(geo_data="Boundaries.geojson",
fill_color='YlOrRd',
fill_opacity=0.5,
line_opacity=1,
data = unlawful_poss_handguns_df,
key_on='feature.properties.dist_num',
columns = ['dist_num', 'unlawful_poss_handguns'],
legend_name="UNLAWFUL POSS OF HANDGUN PER DISTRICT"
).add_to(farthest_block_unlawful_poss_handguns_map )
cursor.execute("""SELECT DISTINCT ST_X(ST_AsText(Where_IS)), ST_Y(ST_AsText(Where_IS)), district from police_stations where district!='Headquarters'""")
police_stations = cursor.fetchall()
gun='%UNLAWFUL POSS OF HANDGUN%'
for police_station in police_stations:
cursor.execute("""SELECT DISTINCT on (A.block) A.district, A.block, A.where_is,
ST_Distance(A.where_is,B.where_is)
FROM crimes as A, police_stations as B
WHERE
A.district=%s and DESCRIPTION::text LIKE %s and B.district= %s
and ST_Distance(A.where_is,B.where_is)
IN
( SELECT max(dist)
FROM
(SELECT ST_Distance(A.where_is,B.where_is) as dist
FROM crimes as A, police_stations as B
WHERE A.district=%s and DESCRIPTION::text LIKE %s and B.district= %s ) as f)
""",[police_station[2],gun,police_station[2],police_station[2],gun, police_station[2]])
farthest_block_unlawful_poss_handguns = cursor.fetchall()
print(farthest_block_unlawful_poss_handguns)
if not farthest_block_unlawful_poss_handguns:
continue
cursor.execute('''
SELECT ST_X(ST_AsText(%s)), ST_Y(ST_AsText(%s))
''',(farthest_block_unlawful_poss_handguns[0][2],farthest_block_unlawful_poss_handguns[0][2]))
farthest_block_unlawful_poss_handguns_location = cursor.fetchall()
folium.Marker(location=(police_station[0],police_station[1]),popup=folium.Popup(html="Police Station <br> District No.:%s <br> Farthest Unlawful Handgun Possion Block:%s"%(farthest_block_unlawful_poss_handguns[0][0],farthest_block_unlawful_poss_handguns[0][1]))).add_to(farthest_block_unlawful_poss_handguns_map)
folium.CircleMarker(farthest_block_unlawful_poss_handguns_location[0],radius=5,color='#ff3187',popup=folium.Popup(html="District No.:%s <br> Block:%s"%(farthest_block_unlawful_poss_handguns[0][0],farthest_block_unlawful_poss_handguns[0][1]))).add_to(farthest_block_unlawful_poss_handguns_map)
farthest_block_unlawful_poss_handguns_map
final_data2['dist_num'] = final_data2['dist_num'].astype(str)
final_data2.head()
gun_crime_location_map = folium.Map(location =(41.8781, -87.6298),zoom_start=11)
folium.Choropleth(geo_data="Boundaries.geojson",
fill_color='YlOrRd',
fill_opacity=0.5,
line_opacity=1,
data = final_data2,
key_on='feature.properties.dist_num',
columns = ['dist_num', 'number_of_gun_crimes'],
legend_name="GUN CRIME"
).add_to(gun_crime_location_map )
cursor.execute("""SELECT ST_X(ST_AsText(Where_IS)), ST_Y(ST_AsText(Where_IS)), district from police_stations where district!='Headquarters'""")
gun='%GUN%'
police_stations = cursor.fetchall()
marker_cluster = MarkerCluster().add_to(gun_crime_location_map)
for police_station in police_stations:
police_station_location = (police_station[0],police_station[1])
cursor.execute("""SELECT DISTINCT ON(caseno) caseno, block, description, count (description), location_description, latitude, longitude from crimes where district=%s and DESCRIPTION::text LIKE %s and (location_description = 'RESIDENCE' or location_description = 'STREET') GROUP BY caseno,block, DESCRIPTION, location_description, latitude, longitude""",[police_station[2],gun])
crimes_per_area = cursor.fetchall()
for crime in crimes_per_area:
if crime[4]=='RESIDENCE':
folium.Marker(location=(crime[5],crime[6]),popup=folium.Popup(html="District No: %s <br> Description: %s <br> Block: %s" %(police_station[2],crime[2],crime[1])),icon=folium.Icon(color='green', icon='ok-sign'),).add_to(marker_cluster)
else:
folium.Marker(location=(crime[5],crime[6]),popup=folium.Popup(html="District No: %s <br> Description: %s<br> Block: %s" %(police_station[2],crime[2],crime[1])),icon=folium.Icon(color='red', icon='ok-sign'),).add_to(marker_cluster)
gun_crime_location_map